iT邦幫忙

第 11 屆 iThome 鐵人賽

DAY 13
0

工具: HeidiSQL

小知識區

count(...)

CRUD:

  • Create
  • Read
  • Update
  • Delete

ws: select權限
wi: insert, update, delete權限

Select 基礎查詢

查詢資料前的基本概念

  1. 表格(table), 記錄(row, record), 欄位(column)
  2. 在設計 table的時候,通常會指定一個欄位為「主索引鍵(primary key)」
  3. 資料型態: 數值, 字串, 日期(預設格式「年-月-日」), NULL(表示「不確定」、「未知」或「沒有」)

查詢敘述

  1. 語法使用順序,使用順序不對會造成錯誤
    1. SELECT
    2. FROM
    3. WHERE
    4. GROUP BY
    5. HAVING
    6. ORDER BY
    7. LIMIT
  2. 指定使用中的資料庫: USE 資料庫名稱
  3. SELECT: 指定需要的欄位
    1. *: 全部
    2. 複數個欄位: 欄位1, 欄位2, 欄位3, ...
  4. FROM: 指定 column, table
    1. 不同資料庫時,可以使用 資料庫2.表格名稱
  5. 數學運算: % 餘數, MOD 餘數, * 乘, / 除, DIV 除整數, + 加, -
  6. 別名 (alias name)
    1. 幫一般欄位取一個欄位別名是比較沒有必要的,如果是運算式的話,通常就要幫它取一個欄位別名來取代原來一大串的運算式。
    2. 別名中,別用 保留字與 空格
    3. 範例: 從 資料庫 cmdev 的 資料表 emp,拿資料並 取別名MonthSalary, !AnnualSalary, !AnnualFullSalary
    SELECT ename, salary AS "MonthSalary",
           salary * 12 AS "AnnualSalary",
           (salary * 12 ) + (salary DIV 2) "AnnualFullSalary"
    FROM cmdev.emp
    

條件查詢 where

  1. 查詢條件: 順序的差異,每次找出最少的資料,以此準則安排條件的順序,對效能會有幫助。
  2. 比較運算子: =, !=, >, >=, <, <=, 其中<=>=的差異在於NULL的判斷
  3. 邏輯運算子: 1. [NOT非], 2. [&&, AND而且], 3. [||, OR或, XOR互斥]
    1. 程式執行順序: ()先算, 由左到右, 1 > 2 > 3 (NOT較少用)
    2. BETWEEN 指定較小的值 AND 指定較大的值: 範圍比較
    3. IN(...): 成員比較
    4. IS: 是
    5. IS NOT: 不是
    6. LIKE: 像
  4. NULL 值判斷
    1. 「NULL」值的判斷,不可以使用比較運算子(<=>除外),判斷一般資料的條件設定,要用邏輯運算子判斷,以下兩種判斷法範例:
    SELECT Name, LifeExpectancy
    FROM country
    WHERE LifeExpectancy IS NULL
    
    SELECT Name, LifeExpectancy
    FROM country
    WHERE LifeExpectancy <=> NULL
    
  5. 字串樣式
    1. %: 0到多個任何字元
    2. _: 一個任何字元
    SELECT
    FROM
    WHERE {欄位|資料|運算式} LIKE '樣版'
    
  6. ORDER BY {欄位||運算式||位置編號} [ASC(預設)|DESC] [,...]

限制查詢

  1. LIMIT: 指定回傳數量
    1. LIMIT 5: 後面一個數字,從最前面拿5筆
    2. LIMIT 5, 5: 後面兩個數字,起始點, 數量; 從第六筆開始拿(不包含第五筆),往後拿五筆
    3. 傳回月薪最高前三名,範例
    SELECT empno, ename, salary
    FROM cmdev.emp
    ORDER BY salary DESC
    LIMIT 3;
    
  2. 排除重複記錄
    1. DISTINCT: 資料庫會特別執行回傳紀錄是否重複的檢查

問題記錄

  • 語法: 大、小寫 對搜尋結果有差異嗎?
    • A: 沒有,搜尋字填大、小寫都得到相同的結果

運算式與函式

資料型態: 數值, 字串, 日期/時間, 空值(使用NULL), 布林值

  1. 數值: 精確值, 近似值( 科學表示法5E3 = 5000)
  2. 字串
  3. 日期/時間: INTERVAL 語法 日期or時間 +/- INTERVAL 數字 單位
  4. NULL 值: 表是未知的記錄

函式

  1. 字串函式:
    1. LOWER(字串): [字串]轉為小寫
    2. UPPER(字串): [字串]轉為大寫
    3. LPAD(字串1, 長度, 字串2), RPAD(字串1, 長度, 字串2): 如果[字串1]的長度小於指定的[長度],就在[字串1]左邊/右邊使用[字串2]補滿
      1. 在處理報表資料的時候,很常用來控制報表內容的格式
    4. LTRIM(字串), RTRIM(字串): 移除[字串]左邊/右邊的空白
    5. TRIM(字串): 移除[字串]左、右的空白
    6. REPEAT(字串, 個數): 重複[字串]指定的[個數]
    7. REPLACE(字串1, 字串2, 字串3): 將[字串1]中的[字串2]替換為[字串3]
  2. 截取字串內容的函式:
    1. LEFT(字串, 長度), RIGHT(字串, 長度): 傳回[字串]左邊/右邊指定[長度]的內容
    2. SUBSTRING(字串, 位置)
    3. SUBSTRING(字串, 位置, 長度)
  3. 連接字串的函式:
    1. CONCAT(參數 [,…]):傳回所有參數連接起來的字串
    2. CONCAT_WS(分隔字串, 參數 [,…]):傳回所有參數連接起來的字串,參數之間插入指定的[分隔字串]
  4. 取得字串資訊的函式:
    1. LENGTH(字串):傳回[字串]的長度(bytes)
    2. CHAR_LENGTH(字串):傳回[字串]的長度(字元個數)
    3. LOCATE(字串1, 字串2):傳回[字串1]在[字串2]中的位置,如果[字串2]中沒有[字串1]指定的內容就傳回0
  5. 數學函式

日期時間函式

  1. 計算日期與時間的函式:
    1. ADDDATE(日期, 天數):傳回[日期]在指定[天數] 以後的日期
    2. ADDDATE(日期, INTERVAL 數字 單位):傳回[日期]在指定[數字]的[單位]以後的日期
    3. ADDTIME(日期時間, INTERVAL數字 單位):傳回[日期時間]在指定[數字]的[單位]以後的日期時間
    4. SUBDATE(日期, 天數):傳回[日期]在指定[天數] 以前的日期
    5. SUBDATE(日期, INTERVAL 數字 單位):傳回[日期]在指定[數字]的[單位]以前的日期
    6. SUBTIME(日期時間, INTERVAL數字 單位):傳回[日期時間]在指定[數字]的[單位]以前的日期時間
    7. DATEDIFF(日期1, 日期2):計算兩個日期差異的天數
    8. 範例:
    SELECT ename, hiredate
    ADDDATE(hiredate, 30),
    ADDDATE(hiredate, INTERVAL 30 DAY)
    FROM cmdev.emp e;
    
  2. 流程控制函式: IF, CASE...WHEN ...THEN

群組查詢

查詢並計算資料的統計分析資訊

  1. 群組函式, 也可以用在日期資料
    SELECT
    SUM(Population) PopSum,
    AVG(Population) PopAvg,
    MAX(Population) PopMax,
    MIN(Population) PopMin,
    COUNT(*) Amount
    FROM country
    
  2. GROUP_CONCAT函式
    SELECT FROUP_CONCAT(DISTINCT Region)
    FROM country
    WHERE Continent = 'Europe'; /* 排除重複的資料後,紙回傳6個區域名稱連接在一起的紀錄 */
    

JOIN 與 UNION 查詢

使用多個表格

Inner Join

通稱內部結合,分成寫在 WHERE後面與 FROM後面

  1. 使用結合條件
    SELECT country.Code, country.Capital, city.Name
    FROM country, city
    WHERE country.Capital = city.ID
    
  2. 指定表格名稱
  3. 表格別名
    SELECT a.name, a.Population
    FROM country a
    
  4. 使用「INNER JOIN」
    SELECT country.Code, country.Capital, city.Name
    FROM country JOIN city ON Capital = city.ID
    

Outer Join (外部結合)

「內部結合」的查詢,一定要符合「結合條件」的資料才會出現
「內部結合」: 包含部門名稱的員工資料,可是沒有分派部門的員工就不用出現了
「外部結合」: 包含部門名稱的員工資料,沒有分派部門的員工也要出現
1. LEFT OUTER JOIN: 以左邊的表格為主
1. RIGHT OUTER JOIN: 以右邊的表格為主
1. 語法範例:
sql SELECT empno, ename, e.deptno, d.dname FROM cmdev.emp e LEFT/RIGHT OUTER JOIN cemdev.dept d ON e.deptno = d.deptno

合併查詢

SELECT 與 SELECT 結合
1.語法:
sql SELECT Region, Name, Papulation FROM country WHERE Region = 'Southeast Asia' AND Population < 2000 UNION SELECT Region, Name, Population FROM country WHERE Region = 'Eastern Asia' AND Population < 1000

  1. 規則:
    1. 回傳結果的欄位名稱,使用第一個查詢的欄位名稱
    2. 所有查詢敘述的欄位數量一定要一樣

上一篇
ColdFusion 練習: webucator.com
下一篇
MySQL
系列文
網頁服務開發之路30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言